package com.misday.eatup.model;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class FoodsDatabaseHelper extends SQLiteOpenHelper {
	
	public static final String CONFLICT_TABEL_NAME = "conflict";
	public static final String UNSUITABLE_TABEL_NAME = "unsuitable";
	
	public static final String DATABASE_NAME = "foods.db";
	public static final int    DATABASE_VERSION = 101;
	
	private static final String TAG = "FoodsDababaseHelper";
	
	private Context mContext;
	
	FoodsDatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		mContext = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		//create conflict table
		db.execSQL("CREATE TABLE " + CONFLICT_TABEL_NAME + " ("
				+ FoodsColumns._ID + " INTEGER PRIMARY KEY,"
				+ FoodsColumns.FOOD1 + " TEXT,"
				+ FoodsColumns.FOOD2 + " TEXT,"
				+ FoodsColumns.PRIORITY + " TEXT,"
				+ FoodsColumns.DETAIL + " TEXT"
				+ ");");
		
		//create unsuitable table
		db.execSQL("CREATE TABLE " + UNSUITABLE_TABEL_NAME + " ("
				+ FoodsColumns._ID + " INTEGER PRIMARY KEY,"
				+ FoodsColumns.FOOD + " TEXT,"
				+ FoodsColumns.PRIORITY + " TEXT,"
				+ FoodsColumns.DETAIL + " TEXT"
				+ ");");
		
		//initConflict(db);
		//initUnsuitable(db);
		
		UpdateDatabase.update(mContext, db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
		
		//drop table
        db.execSQL("DROP TABLE IF EXISTS " + CONFLICT_TABEL_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + UNSUITABLE_TABEL_NAME);
        
        //create table
        onCreate(db);
	}

	private Cursor queryConflict(String food1, String food2) {
		Cursor c;
		String[] columns = new String[] {
				FoodsColumns.FOOD1,
				FoodsColumns.FOOD2,
				FoodsColumns.PRIORITY,
				FoodsColumns.DETAIL
		};
		
		food1 = food1.trim(); //remove white space from beginning and end.
		food2 = food2.trim(); //remove white space from beginning and end.
		
		StringBuffer selectionBuffer = new StringBuffer();
		selectionBuffer.append("(");
		selectionBuffer.append(FoodsColumns.FOOD1 + " LIKE \'%" + food1 + "%\'");
		selectionBuffer.append(" AND ");
		selectionBuffer.append(FoodsColumns.FOOD2 + " LIKE \'%" + food2 + "%\'");
		selectionBuffer.append(") OR (");
		selectionBuffer.append(FoodsColumns.FOOD1 + " LIKE \'%" + food2 + "%\'");
		selectionBuffer.append(" AND ");
		selectionBuffer.append(FoodsColumns.FOOD2 + " LIKE \'%" + food1 + "%\'");
		selectionBuffer.append(")");
		
		//Log.i(TAG, selectionBuffer.toString());
		
		SQLiteDatabase db = this.getReadableDatabase();		
				
		c = db.query(CONFLICT_TABEL_NAME, 
				columns, 
				selectionBuffer.toString(), 
				null, 
				null, 
				null, 
				FoodsColumns.DEFAULT_SORT_ORDER);
				
		return c;
	}
	
	private Cursor queryConflict(String food) {
		Cursor c;
		String[] columns = new String[] {
				FoodsColumns.FOOD1,
				FoodsColumns.FOOD2,
				FoodsColumns.PRIORITY,
				FoodsColumns.DETAIL
		};
		
		food = food.trim(); //remove white space from beginning and end.
		
		StringBuffer selectionBuffer = new StringBuffer();		
		selectionBuffer.append(FoodsColumns.FOOD1 + " LIKE \'%" + food + "%\'");
		selectionBuffer.append(" OR ");
		selectionBuffer.append(FoodsColumns.FOOD2 + " LIKE \'%" + food + "%\'");		
		
		//Log.i(TAG, selectionBuffer.toString());
		
		SQLiteDatabase db = this.getReadableDatabase();		
				
		c = db.query(CONFLICT_TABEL_NAME, 
				columns, 
				selectionBuffer.toString(), 
				null, 
				null, 
				null, 
				FoodsColumns.DEFAULT_SORT_ORDER);
				
		return c;
	}
	
	private Cursor queryUnsuitable(String food) {
		Cursor c;
		String[] columns = new String[] {
				FoodsColumns.FOOD,
				FoodsColumns.PRIORITY,
				FoodsColumns.DETAIL
		};
		
		food = food.trim(); //remove white space from beginning and end.
		
		StringBuffer selectionBuffer = new StringBuffer();
		selectionBuffer.append(FoodsColumns.FOOD + " LIKE \'%" + food + "%\'");
		
		//Log.i(TAG, selectionBuffer.toString());
		
		SQLiteDatabase db = this.getReadableDatabase();		
		
		c = db.query(UNSUITABLE_TABEL_NAME, 
				columns, 
				selectionBuffer.toString(), 
				null, 
				null, 
				null, 
				FoodsColumns.DEFAULT_SORT_ORDER);
		
		return c;
	}
	
	public Cursor query(String food) {
		//return this.queryUnsuitable(food);
		return this.queryConflict(food);
	}
	
	public Cursor query(String food1, String food2) {
		return this.queryConflict(food1, food2);
	}
	
	public void insert(SQLiteDatabase db, String food1, String food2, String priority, String detail) {
		ContentValues values = new ContentValues();
		values.put(FoodsColumns.FOOD1, food1);
		values.put(FoodsColumns.FOOD2, food2);
		values.put(FoodsColumns.PRIORITY, priority);
		values.put(FoodsColumns.DETAIL, detail);		
		
		db.insert(CONFLICT_TABEL_NAME, null, values);
	}
	
	public void insert(String food1, String food2, String priority, String detail) {
		SQLiteDatabase db = this.getWritableDatabase();
		insert(food1, food2, priority, detail);
	}
	
	public void insert(SQLiteDatabase db, String food, String priority, String detail) {
		ContentValues values = new ContentValues();
		values.put(FoodsColumns.FOOD, food);
		values.put(FoodsColumns.PRIORITY, priority);
		values.put(FoodsColumns.DETAIL, detail);
		
		db.insert(UNSUITABLE_TABEL_NAME, null, values);
	}
	
	public void insert(String food, String priority, String detail) {
		SQLiteDatabase db = this.getWritableDatabase();
		insert(db, food, priority, detail);
	}
	
	private void initConflict(SQLiteDatabase db) {
		insert(db, "haha", "hehe", "1", "dead");
		insert(db, "haha", "hoho", "2", "faint");
		insert(db, "hehe", "hoho", "3", "fucked");
	}
	
	private void initUnsuitable(SQLiteDatabase db) {
		insert(db, "haha", "1", "dead");
		insert(db, "hoho", "2", "faint");
		insert(db, "hehe", "3", "fucked");
	}
}
